package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.StaticDao;
import com.mp.approvalreq.entity.*;
import com.mp.approvalreq.util.Formatter;
import com.mp.user.entity.User;
import com.plusmoney.jdbc.util.DBUtils;
import com.plusmoney.util.DateTimeHelper;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.log4j.Logger;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * ���� dao ʵ��
 *
 * @author Administrator
 */
public class StaticDaoImpl implements StaticDao {

	private Logger logger = Logger.getLogger(StaticDaoImpl.class);

	private Connection conn;

	public StaticDaoImpl(Connection connection) {
		this.conn = connection;
	}

	@Override
	public StaticData getLatest10RequestBySelf(StaticData staticData) throws Throwable {
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			String sql = "select Frequest_name,Fcreate_time,Fapplicant " +
					" from approval.request_flow where Fapplicant = ? order by Fcreate_time desc limit 10 ";

			logger.info(Formatter.aformat(sql, staticData.getContactId()));
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, staticData.getContactId());

			List<RequestLite> staticDataList = staticData.getList();
			rs = pstm.executeQuery();

			while (rs.next()) {
				RequestLite requestLite = new RequestLite();
				requestLite.setRequestName("审批");
				requestLite.setTime(rs.getTimestamp(2));
				requestLite.setTimeStr(sdf.format(rs.getTimestamp(2)));
				requestLite.setContent("创建审批-" + rs.getString(1));
				requestLite.setApplicant(rs.getInt(3));

				staticDataList.add(requestLite);
			}
			staticData.setList(staticDataList);
			return staticData;
		} finally {
			DBUtils.releaseResultSet(rs);
			DBUtils.releasePreparedStatement(pstm);
		}
	}


	@Override
	public StaticData getLatest10CommentRequestBySelf(StaticData staticData) throws Throwable {
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			String sql = "select rc.Fcompany_id,rc.Frequest_id,rc.Fcrt_at,rf.Fapplicant from approval.request_comment rc left join approval.request_flow rf on rc.Frequest_id = rf.Frequest_id where rc.Fcrt_by = ? order by rc.Fcrt_at desc limit 10";
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			pstm = conn.prepareStatement(sql);
			logger.info(Formatter.aformat(sql, staticData.getContactId()));
			pstm.setInt(1, staticData.getContactId());
			List<RequestLite> rtlist = staticData.getList();
			rs = pstm.executeQuery();
			while (rs.next()) {
				RequestLite rl = new RequestLite();
				rl.setRequestName("审批");
				rl.setRequestDkey(rs.getInt(2));
				rl.setTime(rs.getTimestamp(3));
				rl.setTimeStr(sdf.format(new Date(rs.getTimestamp(3).getTime())));
				rl.setContent("评论审批");
				rl.setApplicant(rs.getInt(4));
				rtlist.add(rl);
			}
			staticData.setList(rtlist);
			return staticData;
		} finally {
			DBUtils.releaseResultSet(rs);
			DBUtils.releasePreparedStatement(pstm);
		}
	}

	@Override
	public StaticData getLatest10FiledRequestBySelf(StaticData staticData) throws Throwable {
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			String sql = "select rf.Frequest_id,rf.Ffiled_time,f.Fapplicant from " +
					" approval.request_filed rf left join approval.request_flow f " +
					" on rf.Frequest_id = f.Frequest_id where rf.Fwho_filed = ? and rf.Ffiled_status = 2 " +
					" order by rf.Ffiled_time desc limit 10";
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			pstm = conn.prepareStatement(sql);
			logger.info(Formatter.aformat(sql, staticData.getContactId()));
			pstm.setInt(1, staticData.getContactId());
			List<RequestLite> rtlist = staticData.getList();
			rs = pstm.executeQuery();
			while (rs.next()) {
				RequestLite rl = new RequestLite();
				rl.setRequestName("审批");
				rl.setRequestDkey(rs.getInt(2));
				rl.setTime(rs.getTimestamp(2));
				rl.setTimeStr(sdf.format(new Date(rs.getTimestamp(2).getTime())));
				rl.setContent("归档审批");
				rl.setApplicant(rs.getInt(3));
				rtlist.add(rl);
			}
			staticData.setList(rtlist);
			return staticData;
		} finally {
			DBUtils.releaseResultSet(rs);
		}
	}

	@Override
	public List<StaticData> getCurrentObjectNotNullTimesOfCurrentTemplate(Template t, List<Template> list) throws Throwable {
		StringBuilder sb = new StringBuilder("select count(*),sum(c.Fcontent),f.Fapplicant from approval.request_flow f "
				+ " left join approval.request_content c on f.Frequest_id = c.Frequest_id "
				+ " where "
				+ " f.Ffinished in(1,2) and c.Fcontent is not null"
				+ " and f.Fcreate_time > date_format(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') and (");
		for (int i = 0; i < list.size(); i++) {
			Template template = list.get(i);
			int currentNum = Utils.isNull(template.getCurrentNum()) ? 0 : template.getCurrentNum();
			sb.append(Formatter.aformat("(f.Frequest_templet = ? and f.Fcompany_id = ? and c.Fcomponent_num = ?)",
					template.getTemplateId(), template.getCompanyId(), currentNum));
			if (list.size() - i > 1) {
				sb.append(" OR ");
			}
		}
		sb.append(") group by f.Fapplicant");
		String sql = sb.toString();
		List<StaticData> sds = new ArrayList<>();
		try (PreparedStatement pstm = conn.prepareStatement(sql)) {
			logger.info(sql);
			try (ResultSet rs = pstm.executeQuery()) {
				while (rs.next()) {
					StaticData sd = new StaticData();
					sd.setUsedTime(rs.getInt(1));
					sd.setSum(rs.getString(2) == null ? new BigDecimal(0) : rs.getBigDecimal(2).setScale(2, RoundingMode.HALF_UP));
					sd.setContactId(rs.getInt(3));
					sds.add(sd);
				}
			}
		}
		return sds;
	}

	@Override
	public List<Request> selectLast10UserApprovedRequest(Integer companyId, Integer companyInfoId) throws Throwable {
		List<Request> list = new ArrayList<>();
		String sql = "select f.Frequest_id, f.Frequest_name, f.Frequest_templet, f.Fapplicant, f.Fcreate_time,"
				+ " f.Fversion, f.Fouter_position, f.Ffinished, f.Fchecked_time, f.Fall_judger,"
				+ " f.Ftotal_money, flow.is_approved_at"
				+ " from request_flow f"
				+ " left join sys_approval_flow flow on f.Frequest_id = flow.request_id"
				+ " where flow.company_id = ? and flow.company_info_id = ? and f.Fapplicant_ciid != ? and flow.is_approved > 0 and flow.state = 1"
				+ " group by f.Frequest_id order by flow.is_approved_at desc limit 10";

		try (PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setInt(1, companyId);
			ps.setInt(2, companyInfoId);
			ps.setInt(3, companyInfoId);
			logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId, companyInfoId));

			try (ResultSet rs = ps.executeQuery()) {
				while (rs.next()) {
					Request request = new Request();
					request.setRequestDkey(rs.getInt(1));
					request.setRequestName(rs.getString(2));
					request.setTemplateId(rs.getInt(3));
					request.setApplicant(rs.getInt(4));
					request.setRequestTime(rs.getString(5));
					request.setVersion(rs.getInt(6));
					request.setOuterPosition(rs.getString(7));
					request.setFinished(rs.getInt(8));
					request.setCheckedTime(rs.getString(9));
					request.setAllJudger(rs.getString(10));
					request.setTotalMoney(rs.getString(11));
					Timestamp timestamp = rs.getTimestamp(12);
					request.setCheckedTime(Utils.isNull(timestamp) ? null : DateTimeHelper.format(timestamp, DateTimeHelper.DEFAULT_PATTERN));
					list.add(request);
				}
			}
		}
		return list;
	}
}
